# -*_ codeing=utf-8 -*-
# @Time: 2023/1/3 13:51
# @Author: foxhuty
# @File: python_mysql.py
# @Software: PyCharm
# @Based on python 3.10

import mysql.connector
import pymysql

# 方法一：用mysql.connector
conn = mysql.connector.connect(host='localhost', user='root',
                               passwd='foxmmer', database='db_crawling_data',
                               auth_plugin='mysql_native_password')
cursor = conn.cursor()
# 方法二：用pymysql
conn_pymysql = pymysql.connect(host='localhost', user='root', passwd='foxmmer', database='db_crawling_data',
                               port=3306, charset='utf8')
cursor_pymysql = conn_pymysql.cursor()
# 用mysql原生语句
sql = """
create table user(
id int auto_increment primary key,
name varchar(10) not null,
passwd varchar(10) not null
)
"""
sql_insert = "insert into user(name,passwd)values('tommy','123456')"
sql_insert_many = "insert into user(name,passwd) values(%s,%s)"
val = [['jane', '23456'], ['miaom', '123456']]  #也可以用元组列表
sql_query = "select * from user"
cursor.executemany(sql_insert_many, val)
conn.commit()
cursor.execute(sql_query)
tb = cursor.fetchall()
for i in tb:
    print(i[1])
cursor.close()
conn.close()
